﻿using System;
using System.Linq;
using System.Text;
using CharacterCreation.Data.Annotations;
using System.Reflection;

namespace CharacterCreation.Data.QueryBuilders {
    public class TableBuilder {
        public static TableAttribute GetTableAttribute<T>() {
            // Get the implemented table attribute.
            TableAttribute tableAttribute = (TableAttribute)typeof(T).GetCustomAttributes(typeof(TableAttribute), false).FirstOrDefault();

            // If there is no table attribute specified take the name of the class.
            if (tableAttribute == null) {
                tableAttribute = new TableAttribute();
                tableAttribute.TableName = typeof(T).Name;
            }

            return tableAttribute;
        }

        public static ColumnAttribute GetColumnAttribute<T>(PropertyInfo pInfo) {
            if (pInfo.GetCustomAttributes(typeof(IgnoreAttribute), false).FirstOrDefault() != null) return null;

            ColumnAttribute columnAttribute = (ColumnAttribute)pInfo
                .GetCustomAttributes(typeof(ColumnAttribute), true).FirstOrDefault();

            if (columnAttribute == null) {
                columnAttribute = new ColumnAttribute();
                // Set the column name
                columnAttribute.ColumnName = pInfo.Name;

                // Set the nullable attribute
                if (columnAttribute.ColumnName.ToLower() == "id") columnAttribute.Nullable = "primary key";
                else columnAttribute.Nullable = "null";

                // Set the sql type
                columnAttribute.SqlType = getSqlDataType(pInfo);
            }

            return columnAttribute;
        }

        public static string CreateTableQuery<T>() {
            TableAttribute tableAttribute = TableBuilder.GetTableAttribute<T>();

            StringBuilder builder = new StringBuilder();
            StringBuilder columnDefinition = new StringBuilder();

            foreach (var pInfo in typeof(T).GetProperties()) {

                if (pInfo.GetCustomAttributes(typeof(IgnoreAttribute), false).FirstOrDefault() != null) continue;

                ColumnAttribute columnAttribute = (ColumnAttribute)pInfo
                    .GetCustomAttributes(typeof(ColumnAttribute), true).FirstOrDefault();

                if (columnAttribute == null) {
                    columnAttribute = new ColumnAttribute();
                    // Set the column name
                    columnAttribute.ColumnName = pInfo.Name;

                    // Set the nullable attribute
                    if (columnAttribute.ColumnName.ToLower() == "id") columnAttribute.Nullable = "primary key";
                    else columnAttribute.Nullable = "null";

                    // Set the sql type
                    columnAttribute.SqlType = getSqlDataType(pInfo);
                }

                if (!string.IsNullOrWhiteSpace(columnDefinition.ToString())) columnDefinition.Append("," + Environment.NewLine);

                columnDefinition.AppendFormat("[{0}] {1} {2}",
                    columnAttribute.ColumnName,
                    columnAttribute.SqlType,
                    columnAttribute.Nullable);
            }

            string drop = string.Format("if exists (select * from sys.tables where object_id = object_id('{0}')) {1} drop table [dbo].[{0}] {1}; {1}", tableAttribute.TableName, Environment.NewLine);
            builder.AppendFormat("{0} create table [dbo].[{1}] ( {2} );", drop, tableAttribute.TableName, columnDefinition.ToString());
            return builder.ToString();
        }

        public static string DropTableQuery<T>() {
            TableAttribute tableAttribute = TableBuilder.GetTableAttribute<T>();
            return string.Format("drop table {0}", tableAttribute.TableName);
        }

        public static string TableExistsQuery<T>() {
            TableAttribute tableAttribute = TableBuilder.GetTableAttribute<T>();
            return string.Format("if exists (select * from sys.tables where object_id = object_id('{0}')) select cast(1 as bit) else select cast(0 as bit)", tableAttribute.TableName);
        }

        private static string getSqlDataType(System.Reflection.PropertyInfo pInfo) {
            switch (pInfo.PropertyType.ToString()) {
                case "System.String":
                    return "nvarchar(MAX)";
                case "System.Int16":
                case "System.Int32":
                case "System.Int64":
                    return "int";
                case "System.Boolean":
                    return "bit";
                case "System.DateTime":
                    return "datetime";
                case "System.Guid":
                    return "uniqueidentifier";
                default:
                    return "nvarchar(MAX)";
            }
        }
    }
}
